Introduction to PostGIS

class: center, middle, inverse, title-slide

# Introduction to PostGIS
## Relational databases & GIS
### Andreas Scharmüller
### AG Landscape Ecology
### 2021-11-13 (updated: 2021-11-14)

---

background-image: url(data:image/png;base64,#https://d1.awsstatic.com/rdsImages/postgresql_logo.6de4615badd99412268bc6aa8fc958a0f403dd41.png)
background-position: 80% 80%

## What is PostGIS ?

- It's FOSS!

- Spatial extension for the relational database PostgreSQL

- Compliant with the Open Geospatial Consortium (OGC) standards
 <https://www.ogc.org>

- Simple Feature Access
 <https://en.wikipedia.org/wiki/Simple_Features>
 
 - `ST_Area()`
 - `ST_Length()`
 - `ST_Intersection()`
 - `ST_Intersects()`
 - `...`

![](data:image/png;base64,#https://www.osgeo.org/wp-content/themes/roots/assets/img/logo-osgeo.svg)

---
background-image: url(data:image/png;base64,#https://upload.wikimedia.org/wikipedia/commons/7/7b/Logo_square_postgis.png)
background-position: 80% 80%

## What is PostGIS ?

- Spatial extension for the relational database PostgreSQL

- Geospatial Data Type
        - Point
        - Line
        - Polygon
        - Geometry Collection
        - Raster
        
    - (Spatial) Indices

![](data:image/png;base64,#https://upload.wikimedia.org/wikipedia/commons/thumb/1/1b/R_logo.svg/310px-R_logo.svg.png)

---
## Why not use a Shapefile?

.pull-left[
### ArcGIS (proprietary)

![](data:image/png;base64,#https://desktop.arcgis.com/en/arcmap/10.3/manage-data/shapefiles/GUID-AFEE52B3-BBE8-4F33-A55B-5CFD5106B80C-web.png)

```
## cities.dbf
## cities.prj
## cities.shp
## cities.shx
```

]

.pull-right[
### PostGIS
![](data:image/png;base64,#data/postgis_table_example.png)
- Geo-Information

- __One table__
    - __One Column__

]

---
## Why not use a Shapefile?

.pull-left[

- Easy data storage (comapared on standard GIS software)

- Standard simple feature formatting allows spatial data to be accessed by other software

- Automation of processes

- Handling of large data-sets

]

.pull-right[
### PostGIS
![](data:image/png;base64,#data/postgis_table_example.png)
- Geo-Information

- __One table__
    - __One Column__

]

---

![](data:image/png;base64,#https://raw.githubusercontent.com/allisonhorst/stats-illustrations/master/rstats-artwork/sf.png)

Illustration (c) by [Allison Horst](https://github.com/allisonhorst/stats-illustrations/blob/master/rstats-artwork/sf.png)

---
# Geo Libraries

- [GDAL](https://gdal.org) - Geo Data Abstraction Library

![](data:image/png;base64,#https://gdal.org/_static/gdalicon.png)

- [GEOS](https://libgeos.org) - Geometry Engine, Open Source

![](data:image/png;base64,#https://libgeos.org/web-logo.png)

- [PROJ](https://proj.org)

![](data:image/png;base64,#https://proj.org/_static/logo.png)

---
class: center, inverse, middle
## PostGIS functions

---
## Palar River Example

![](data:image/png;base64,#https://www.turismofvg.it/proxyvfs.axd/popup/r94157/file-jpg)
---
## Palar River Example

|  length|     pente| strahler|fid     |geom                           |
|-------:|---------:|--------:|:-------|:------------------------------|
| 3053.50| 20.366169|        1|1194653 |LINESTRING (4550796 2582247... |
|  986.99|  5.996377|        2|1194668 |LINESTRING (4553302 2582898... |
|  476.30| 11.744198|        2|1194670 |LINESTRING (4552910 2583143... |

![](data:image/png;base64,#/home/scharmueller/Projects/teaching/docs/PostGIS-intro_files/figure-html/unnamed-chunk-3-1.png)

---
## Union

```sql
SELECT ST_Union(geom) AS geom
FROM palar
```

```r
palar |>
  st_union()
```

![](data:image/png;base64,#/home/scharmueller/Projects/teaching/docs/PostGIS-intro_files/figure-html/unnamed-chunk-6-1.png)

---
## Length

```sql
SELECT ST_Length(ST_Union(geom)) geom
FROM palar
```

```r
palar |>
  st_union() |>
  st_length()
```

```
## 23289.93 [m]
```

---
## Buffer - 100m

```sql
SELECT ST_Buffer(ST_Union(geom), 100) AS geom_buf
FROM palar
```

```r
palar |>
  st_union() |>
  st_buffer(100)
```

![](data:image/png;base64,#/home/scharmueller/Projects/teaching/docs/PostGIS-intro_files/figure-html/unnamed-chunk-11-1.png)

---
## Area

```sql
SELECT ST_Area(ST_Buffer(ST_Union(geom), 100)) AS area
FROM palar
```

```r
palar |>
  st_union() |>
  st_buffer(100) |>
  st_area()
```

```
## 4615553 [m^2]
```

---
## Convex Hull

```sql
SELECT ST_ConvexHull(geom) AS geom
FROM palar
```

```r
palar |>
  st_union() |>
  st_convex_hull()
```

![](data:image/png;base64,#/home/scharmueller/Projects/teaching/docs/PostGIS-intro_files/figure-html/unnamed-chunk-16-1.png)

---
## Projections

```sql
SELECT ST_SRID(geom)
FROM palar
LIMIT 1
```

```r
st_crs(palar) # 3035
```

### Transform

```sql
CREATE TABLE palar2 AS 
  SELECT fid, ST_Transform(geom, 4326) geom -- EPSG for WGS84
  FROM palar
```

<https://epsg.io/4326>

---
## Intersection

- Aim: Intersect 500m River Buffer with [CORINE - LULC](https://land.copernicus.eu/pan-european/corine-land-cover) data

![](data:image/png;base64,#/home/scharmueller/Projects/teaching/docs/PostGIS-intro_files/figure-html/unnamed-chunk-20-1.png)

---
## Intersection

```sql
SELECT
  cor.label, cor.label,
  ST_Intersection(ST_Buffer(riv.geom, 500), cor.geom) geom
FROM
  palar riv,
  corine cor
WHERE ST_DWithin(tbl1.geom, tbl2.geom, 500)
```

![](data:image/png;base64,#/home/scharmueller/Projects/teaching/docs/PostGIS-intro_files/figure-html/unnamed-chunk-22-1.png)

---
## Intersection

```r
st_intersection(st_buffer(st_union(palar), 500), corine)
```

---
class: center, inverse, middle
# Idexing

---
## Index

- Extremely important for performance!

- Different Indices for different data types

- PRIMARY KEY
    
    - BTREE
    
    - __GIST__ - Geographic Data

![](data:image/png;base64,#http://postgis.net/workshops/postgis-intro/_images/bbox.png)

<http://postgis.net/workshops/postgis-intro/indexing.html>

---
## Index

---
## Resources

- R sf cheatsheet

<https://raw.githubusercontent.com/rstudio/cheatsheets/main/sf.pdf>

- Gentle Introduction to PostGIS

<https://medium.com/innovation-and-technology/part-1-postgis-at-the-city-of-boston-9476293d71c2>

<https://medium.com/@paylakatel/part-2-postgis-at-the-city-of-boston-711cf30cf1f3>

<https://medium.com/@paylakatel/part-3-postgis-at-the-city-of-boston-98b83b0d1503>

---
## Slides

- OLAT
- <https://andschar.github.io/teaching/PostGIS-intro.html>

### Made with
- <https://github.com/rstudio/rmarkdown>
- <https://github.com/yihui/knitr>
- <https://github.com/yihui/xaringan>

---
layout: false
# [Introduction to Git & GitHub](https://andschar.github.io/teaching/GIT.html)

## Thank you for your attention!

### Material: <https://andschar.github.io/teaching>

__Andreas Scharmüller__

Quantitative Landscape Ecology 
iES Landau, Institute for Environmental Sciences 
University of Koblenz-Landau 
University of Strasbourg

<img src="data:image/png;base64,#https://upload.wikimedia.org/wikipedia/de/thumb/9/9f/Twitter_bird_logo_2012.svg/1259px-Twitter_bird_logo_2012.svg.png" width="20"> @andschar 
<img src="https://seeklogo.com/images/M/mail-icon-logo-28FE0635D0-seeklogo.com.png" width="20"> andschar@protonmail.com